package com.zkah.myfirstpoi.service.impl;

import com.zkah.myfirstpoi.common.MyException;
import com.zkah.myfirstpoi.mapper.UserMapper;
import com.zkah.myfirstpoi.pojo.User;
import com.zkah.myfirstpoi.service.UserService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Service("userService")
public class UserServiceImpl implements UserService{
    @Resource
    private UserMapper userMapper;
    @Override
    public List<User> selectAllUser() {
        List<User> userList = userMapper.selectAllUser();
        return userList;
    }

    @Override
    @Transactional(readOnly = false,rollbackFor = Exception.class)
    public boolean batchImport(String fileName, MultipartFile file) throws Exception {
        boolean notNull = false;
        List<User> userList = new ArrayList<>();
        if(!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")){
            throw new MyException("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if(isExcel2003){
            wb = new HSSFWorkbook(is);
        }else{
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull=true;
        }
        User user;
        /**
         * i=2 表示从第三行开始循环 如果excel表中 第三行开始才是需要导入的数据
         */
        for(int i=2;i<=sheet.getLastRowNum();i++){
            //通过sheet表单对象得到行对象
            Row row = sheet.getRow(i);
            if(row==null){
                continue;
            }
            //sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException
            int num = sheet.getLastRowNum();
            user = new User();
            //String type = row.getCell(0).getCellTypeEnum().name();
            //循环，得到每一行 第一个 单元格 判断完单元格的类型再去赋值
            if(!row.getCell(0).getCellTypeEnum().name().equals("STRING")){
                throw new MyException("导入失败(第"+(i+1)+"行,用户名请设为文本格式)");
            }
            String userName = row.getCell(0).getStringCellValue();
            if(userName==null||userName.isEmpty()){
                throw new MyException("导入失败(第"+(i+1)+"行,用户名未填写)");
            }

            //  每一行的第二个单元格  单元格是数字 必须设置单元格格式 为string 否则报错
            row.getCell(1).setCellType(CellType.STRING);
           // String type1 = row.getCell(1).getCellTypeEnum().name();
            String password = row.getCell(1).getStringCellValue();

            if(password==null || password.isEmpty()){
                throw new MyException("导入失败(第"+(i+1)+"行,密码未填写)");
            }
            //完整循环一次得到一个user对象
            user.setUsername(userName);
            user.setPassword(password);
            userList.add(user);
        }

        for(User userResord:userList){
            String name = userResord.getUsername();
            int num = userMapper.selectByName(name);
            if(num==0){
                userMapper.addUser(userResord);
                System.out.println("插入："+userResord.getUsername());
            }else{
                userMapper.updateUserByName(userResord);
                System.out.println("更新："+userResord.getUsername());
            }
        }
        return notNull;
    }
}
